"""
Case Type   : 系统内部使用工具
Case Name   : 创建分区表
Create At   : 2022/03/03
Owner       : zou_jialiang050
Description :
    1.构建数据
    2.清理环境
Expect      :
    1.构建成功
    2. 成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.Common import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Constant = Constant()


class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pricomsh = CommonSH("PrimaryDbUser")
        self.primary_node = Node('PrimaryDbUser')
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "source_data")

    def test_ustore(self):
        text = f"----step1:创建分区表:expect:成功;----"
        self.log.info(text)
        file_lis = ['tab_ap_join_03.txt']
        for file in file_lis:
            self.common.scp_file(self.primary_node, f"{file}", self.target_path)

        sql1 = f"""drop table if exists tab_ap_join_03;
        create table tab_ap_join_03 (
          col_tinyint       tinyint,
          col_smallint      smallint,
          col_int        integer,
          col_bigint       bigint,
          col_numeric       numeric,
          col_double       double precision,
          col_decimal          decimal,
          col_varchar        varchar(100),
          col_char       char(30),
          col_nvarchar2      nvarchar2(100),
          col_text       varchar(2000),
          col_timestamptz   varchar(100),
          col_timestamp   varchar(100),
          col_interval      varchar(100),
          col_date          date
        )
        with (storage_type=ustore) partition by range (col_date)
        (
        partition tab_ap_join_03_001 values less than('2010-02-09'),
        partition tab_ap_join_03_002 values less than('2010-02-26'),
        partition tab_ap_join_03_003 values less than('2010-03-15'),
        partition tab_ap_join_03_004 values less than('2010-04-01'),
        partition tab_ap_join_03_005 values less than('2010-04-18'),
        partition tab_ap_join_03_006 values less than('2010-05-05'),
        partition tab_ap_join_03_007 values less than('2010-05-22'),
        partition tab_ap_join_03_008 values less than('2010-06-08'),
        partition tab_ap_join_03_009 values less than('2010-06-25'),
        partition tab_ap_join_03_010 values less than('2010-07-12'),
        partition tab_ap_join_03_011 values less than('2010-07-29'),
        partition tab_ap_join_03_012 values less than('2010-08-15'),
        partition tab_ap_join_03_013 values less than('2010-09-01'),
        partition tab_ap_join_03_014 values less than('2010-09-18'),
        partition tab_ap_join_03_015 values less than('2010-10-05'),
        partition tab_ap_join_03_016 values less than('2010-10-22'),
        partition tab_ap_join_03_017 values less than('2010-11-08'),
        partition tab_ap_join_03_018 values less than('2010-11-25'),
        partition tab_ap_join_03_019 values less than('2010-12-12'),
        partition tab_ap_join_03_020 values less than('2010-12-29'),
        partition tab_ap_join_03_021 values less than('2011-01-15'),
        partition tab_ap_join_03_022 values less than('2011-02-01'),
        partition tab_ap_join_03_023 values less than('2011-02-18'),
        partition tab_ap_join_03_024 values less than('2011-03-07'),
        partition tab_ap_join_03_025 values less than('2011-03-24'),
        partition tab_ap_join_03_026 values less than('2011-04-10'),
        partition tab_ap_join_03_027 values less than('2011-04-27'),
        partition tab_ap_join_03_028 values less than('2011-05-14'),
        partition tab_ap_join_03_029 values less than('2011-05-31'),
        partition tab_ap_join_03_030 values less than('2011-06-17'),
        partition tab_ap_join_03_031 values less than('2011-07-04'),
        partition tab_ap_join_03_032 values less than('2011-07-21'),
        partition tab_ap_join_03_033 values less than('2011-08-07'),
        partition tab_ap_join_03_034 values less than('2011-08-24'),
        partition tab_ap_join_03_035 values less than('2011-09-10'),
        partition tab_ap_join_03_036 values less than('2011-09-27'),
        partition tab_ap_join_03_037 values less than('2011-10-14'),
        partition tab_ap_join_03_038 values less than('2011-10-31'),
        partition tab_ap_join_03_039 values less than('2011-11-17'),
        partition tab_ap_join_03_040 values less than('2011-12-04'),
        partition tab_ap_join_03_041 values less than('2011-12-21'),
        partition tab_ap_join_03_042 values less than('2012-01-07'),
        partition tab_ap_join_03_043 values less than('2012-01-24'),
        partition tab_ap_join_03_044 values less than('2012-02-10'),
        partition tab_ap_join_03_045 values less than (maxvalue)
        );
        
        copy tab_ap_join_03 from 
        '{os.path.join(self.target_path, 'tab_ap_join_03.txt')}' delimiters  ',';
        """

        res1 = self.pricomsh.execut_db_sql(sql1)
        self.log.info(res1)

        self.assertIn('CREATE TABLE', res1, 'sql1执行失败')
        self.assertIn('COPY 3000', res1, 'sql1执行失败')

    def tearDown(self):
        text = f"----step2:清理环境:expect:成功;----"
        self.log.info(text)
        del_flag = False
        del_cmd = f'rm -rf {self.target_path};' \
                  f'if [ -d {self.target_path} ]; ' \
                  f'then echo "exists";else echo "not exists"; fi'
        for num in range(10):
            del_res = self.common.get_sh_result(self.primary_node, del_cmd)
            if 'not exists' in del_res:
                del_flag = True
                break
        self.assertTrue(del_flag, '文件删除失败')
        self.log.info(f'----{os.path.basename(__file__)} finish----')
